IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageRevisionSelectAll]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageRevisionSelectAll]
GO
create procedure [dbo].[cms_sp_PageRevisionSelectAll]
(
@PageId int,
@SiteGroupId int = null,
@SiteId int = null,
@SystemLanguageId int = null,
@PageTypeParameter nvarchar(400)
)
as
declare @PublicPageId int
select
	@PublicPageId = PublicPageId
from dbo.Pages
where PageId = @PageId
select 
	Pages.PageId,
	Pages.PublicPageId,	
	Pages.SiteGroupId,
	Pages.SiteId,
	Pages.SystemLanguageId,	
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.PageNodeId.GetLevel() as PageLevel,	
	Pages.PageName,
	Pages.PageUrl,
	Pages.PageBehaviourTypeKey,
	Pages.PageTypeKey,
	Pages.IsVisible,
	Pages.UseFileCache,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	Creators.UserName as UserCreatedBy,
	Updaters.UserName as UserLastUpdatedBy,
	EntityRevisions.IsPublished,
	EntityRevisions.PublishStartDate,
	EntityRevisions.PublishEndDate,
	EntityRevisions.IsLatestRevision,
	EntityRevisions.IsLatestCompletedRevision,
	EntityRevisions.IsDeleted
from dbo.Pages
inner join [dbo].[EntityRevisions]
	on EntityRevisions.EntityId = Pages.PageId and 
	EntityRevisions.EntityTypeParameter = @PageTypeParameter
left join [dbo].[Users] as Creators
	on Creators.UserId = Pages.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = Pages.LastUpdatedBy
where Pages.PublicPageId = @PublicPageId and
	((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId)) and 
	((Pages.SystemLanguageId is null) or
	(Pages.SystemLanguageId = @SystemLanguageId))
order by PageId desc


GO

